package com.es.pay.ctmGrade;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class CtmGradeBiz {

	public PageBean queryCtmGradeList(PageNavigator pageNav, ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		PageBean pageBean = new  PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select ctm_grade_id,yyyy_mm,emp_code,emp_id,emp_name,emp_area_value,emp_area_code,emp_rtm_name,emp_rtm_code,gradea,gradeb,gradec,graded,gradee,gradef,gradeg,grade_totall "
						+" from hrm_ctm_grade where '1'='1'";
			String[] sqlArrary	= new String[] {"and  yyyy_mm=? ",
						"and emp_area_code = ? ",
						"and UPPER(emp_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(emp_code) like '%'+UPPER(?)+'%' ",
						"and UPPER(emp_rtm_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(emp_rtm_code) like '%'+UPPER(?)+'%' "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			int total = dao.getAllRow(sql,query);		
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total%pageNav.getPageRow()==0?total/pageNav.getPageRow():total/pageNav.getPageRow()+1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally{
			dao.close();
		}
		return pageBean;
	}
	
	public ArrayList<HashMap<String,String>> queryCtmGradeList( ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select yyyy_mm ,emp_code,emp_name,emp_area_value,emp_rtm_name,gradea,gradeb,gradec,graded,gradee,gradef,gradeg,grade_totall "
						+" from hrm_ctm_grade where '1'='1'";
			String[] sqlArrary	= new String[] {"and  yyyy_mm=? ",
						"and emp_area_code = ? ",
						"and UPPER(emp_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(emp_code) like '%'+UPPER(?)+'%' ",
						"and UPPER(emp_rtm_name) like '%'+UPPER(?)+'%' ",
						"and UPPER(emp_rtm_code) like '%'+UPPER(?)+'%' "};
			for (int i = 0; i < param.size(); ++i) {
			      if(!param.get(i).equals("")){
			    	  sql += sqlArrary[i];
			    	  query.add(param.get(i).trim());
			      }
			  }
			ResultSet resultSet = null;
			if(query.size()==0)
				resultSet = dao.Select(sql);
			else
				resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData();
			
			while(resultSet.next()){
					HashMap<String,String> map = new HashMap<String,String>();
					for(int i = 0;i<rss.getColumnCount(); i++){ 	
						map.put(rss.getColumnName(i+1), (String) resultSet.getObject(rss.getColumnName(i+1)));							
				 }	
				  list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}
	
	public void checkCtmGrade() throws Exception{
		Dao dao = new Dao();
		try {
			String sql = "insert into hrm_ctm_grade(emp_id,yyyy_mm,emp_code,emp_name,emp_area_value,emp_area_code,emp_rtm_name,emp_rtm_code) "
						+" select emp_id ,convert(varchar(7),getdate(),23) yyyy_mm,emp.emp_code,emp.emp_name,emp.emp_area_value,emp_area_code,emp_rtm_name,(select top 1 a.emp_code from hrm_emp_info  a where a.emp_id = emp.emp_rtm_code) emp_rtm_code" 
						+" from hrm_emp_info emp where emp.emp_role='CTM' and emp.emp_shiyebu='ITMALL' and emp.emp_rtm_code is not  null"
						+" and  not exists (select * from hrm_ctm_grade where yyyy_mm = convert(varchar(7),getdate(),23)) ";
			dao.update(sql, null);
		} finally{
			dao.close();
		}
	}
	
	public String getYyyyMm() throws Exception{
		Dao dao = new Dao();
		String yyyyMm="";
		try {
			String sql = "select convert(varchar(7),getdate(),23) yyyy_mm";
			ResultSet resultSet = dao.Select(sql);
			if(resultSet.next())
				yyyyMm = resultSet.getString(1);
		} finally{
			dao.close();
		}
		return yyyyMm;
	}
	
	public boolean updateCtmGrade(ArrayList<String> param) throws Exception{
		Dao dao = new Dao();
		boolean bool = false;
		try {
			String sql = "update hrm_ctm_grade set gradea=?,gradeb=?,gradec=?,graded=?,gradee=?,gradef=?,gradeg=?,grade_totall=?  where yyyy_mm = convert(varchar(7),getdate(),23) and  ctm_grade_id =?";
			dao.update(sql, param);
			bool = true;
		}catch(Exception ex){
			throw ex;
		} finally{
			dao.close();
		}
		return bool;
	}
	
	public CtmGradeBean getCtmGrade(String ctm_grade_id) throws Exception{
		Dao dao = new Dao();
		CtmGradeBean bean = new CtmGradeBean();
		ArrayList<String> query = new ArrayList<String>();
		query.add(ctm_grade_id);
		try {
			String sql = "select * from hrm_ctm_grade where yyyy_mm = convert(varchar(7),getdate(),23) and  ctm_grade_id =?";
			ResultSet resultSet = dao.Select(sql,query);
			ResultSetMetaData rss = resultSet.getMetaData(); 
			if(resultSet.next()){
				  for(int i = 0;i<rss.getColumnCount(); i++){ 	
					  if(rss.getColumnName(i+1).equals("ctm_grade_id"))
						  bean.setCtm_grade_id(resultSet.getLong("ctm_grade_id"));
					  else if(rss.getColumnName(i+1).equals("emp_id"))
						  bean.setEmp_id(resultSet.getLong("emp_id"));
					  else 
						  ctmGradeInfoBeanSet(bean,rss.getColumnName(i+1),(String) resultSet.getObject(rss.getColumnName(i+1)));
				 }	
			  }
		} finally{
			dao.close();
		}
		return bean;
	}
	
	private void ctmGradeInfoBeanSet(CtmGradeBean bean,String field,String value) throws SecurityException, ClassNotFoundException, IllegalArgumentException, IllegalAccessException{
		Field[] fds = Class.forName("com.es.pay.ctmGrade.CtmGradeBean").getDeclaredFields();   
		
		for(int i=0;i<fds.length;i++){   
			if(fds[i].getName().equals(field)){
				fds[i].set(bean, value);
				return;
			}
		}
	}
}
